/**
 * 
 */
package org.jint.adsltest.analysis;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.HttpURLConnection;
import java.net.MalformedURLException;
import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Iterator;

import org.json.JSONException;
import org.json.JSONObject;

/**
 * @author jint
 * 
 */
public class Data {

	private static String[] placeList = "中交一公院".split(",");

	/**
	 * @param args
	 */
	public static void main(String[] args) {

		//getTestByAll();
		getTestBySuite();
	}

	private static void getTestBySuite() {
		String StartDay = "20101123";
		JSONObject suiteList = getTestSuite(StartDay);

		Iterator<String> keys = suiteList.keys();

		while (keys.hasNext()) {
			try {
				String key = keys.next();
				String value = suiteList.getString(key);
				String[] suite = value.split(",");
				
				String place = suite[0];
				String roundNo = suite[1];
				getTestData(place, roundNo);
				
				try {
					Thread.sleep(2000);
				} catch (InterruptedException e) {
					e.printStackTrace();
				}
			} catch (JSONException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}

	}

	private static JSONObject getTestSuite(String startDay) {
		String strUrl = "http://kuandaiceshi.appspot.com/getsuite?count=100&day="
				+ startDay + "&mode=debug";
		System.out.println(strUrl);

		strUtil.printInfo("Request TestSuite List");

		String jsonData = getDataFromGae(strUrl);
		// String jsonData = "{}";

		JSONObject jsonObj = null;
		if (jsonData.length() > 10) {
			try {
				jsonObj = new JSONObject(jsonData);
			} catch (JSONException e) {
				e.printStackTrace();
			}
		} else {
			strUtil.printInfo("That TestSuite not Exist!");
		}
		strUtil.printInfo("Request TestSuite Success.\n");
		return jsonObj;
	}

	private static void getTestByAll() {
		String StartDay = "20101116";
		String[] arrayNo = makeRoundNo(StartDay, 10);

		if (null != arrayNo) {

			for (int i = 0; i < arrayNo.length; i++) {
				for (int j = 0; j < placeList.length; j++) {
					strUtil.printInfo("Process TestSuite :" + placeList[j] + " "
							+ arrayNo[i]);
					getTestData(placeList[j], arrayNo[i]);
					try {
						Thread.sleep(4000);
					} catch (InterruptedException e) {
						e.printStackTrace();
					}
				}
			}
		}
	}

	private static String[] makeRoundNo(String startDay, int days) {

		String[] ret = null;
		String[] hour24 = "00,01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23"
				.split(",");
		int arrayLen = days * hour24.length;

		int len = startDay.length();
		if (8 == len || 10 == len) {
			String year = startDay.substring(0, 4);
			String month = startDay.substring(4, 6);
			String day = startDay.substring(6, 8);

			SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
			Calendar cal = Calendar.getInstance();
			cal.set(Calendar.YEAR, Integer.parseInt(year));
			cal.set(Calendar.MONTH, Integer.parseInt(month) - 1);
			cal.set(Calendar.DAY_OF_MONTH, Integer.parseInt(day));

			ret = new String[arrayLen];

			for (int i = 0; i < days; i++) {
				String DATEFORMAT = "yyyyMMdd";
				SimpleDateFormat formatter = new SimpleDateFormat(DATEFORMAT);
				String strDate = formatter.format(cal.getTime());

				for (int j = 0; j < hour24.length; j++) {
					ret[i * 24 + j] = strDate + hour24[j];
				}

				cal.add(Calendar.DATE, -1);
			}
		} else {
			// 格式错误，不执行任何操作
		}

		return ret;
	}

	public static void getTestData(String place, String roundNo) {

		// String place = "中交一公院";
		// String roundNo = "2010112006";
		String strUrl = "http://kuandaiceshi.appspot.com/getdata?roundno="
				+ roundNo + "&place=" + place + "&mode=debug";
		System.out.println(strUrl);

		strUtil.printInfo("Request TestDetail :" + place + " " + roundNo);

		String jsonData = getDataFromGae(strUrl);
		// String jsonData = "{}";

		if (jsonData.length() > 10) {
			try {
				JSONObject jsonObj = new JSONObject(jsonData);
				saveData(place, roundNo, jsonObj);
			} catch (JSONException e) {
				e.printStackTrace();
			}
		} else {
			strUtil.printInfo("That TestDetail not Exist!");
		}

	}



	private static void saveData(String place, String roundNo, JSONObject detail) {
		Connection conn = null;
		PreparedStatement stmt = null;
		// PreparedStatement stmt2 = null;
		String sql = "";
		int suiteId = 1;
		try {
//			// 连接SQLite的JDBC
//			Class.forName("org.sqlite.JDBC");
//
//			// 数据库名db/testdata.db的连接，如果不存在就在当前目录下创建之
//			conn = DriverManager.getConnection("jdbc:sqlite:db/testdata.db");
			conn = ConnectionUtil.getCon();

			Statement stat = conn.createStatement();
			sql = "select * " + "from PageTestSuite where place='" + place
					+ "' and roundNo='" + roundNo + "'";
			ResultSet rs2 = stat.executeQuery(sql); // 看看数据是否和数据库中的数据重复了
			if (rs2.next()) { // 
				strUtil.printInfo("");
				strUtil.printInfo("That TestSuite " + place + " " + roundNo
						+ " already in DB:");
				rs2.close();

				sql = "select count(id) as testCount "
						+ "from PageTestDetail where place='" + place
						+ "' and roundNo='" + roundNo + "'";
				rs2 = stat.executeQuery(sql);
				if (rs2.next()) {
					int count = rs2.getInt("testCount");
					strUtil.printInfo("    has " + count + " record in DB, "
							+ detail.length() + " record in Cloud");
				}
				rs2.close();
			} else {

				conn.setAutoCommit(false);
				sql = "insert into PageTestSuite (username,place,roundNo,starttime,endtime,casecount,status) "
						+ "values(?,?,?,?,?,?,?);";
				stmt = conn.prepareStatement(sql);

				stmt.setString(1, "Jint");
				stmt.setString(2, place);
				stmt.setString(3, roundNo);
				stmt.setString(4, "starttime");
				stmt.setString(5, "endtime");
				stmt.setInt(6, detail.length());
				stmt.setString(7, "download OK");
				stmt.execute();

				try {
					conn.commit();
				} catch (SQLException e) {
					e.printStackTrace();
					conn.rollback(); // 操作不成功，回滚事务
				}
				stmt.close();

				// 获得自动生成的 suiteId
				sql = "select max(id) as suiteId from PageTestSuite";
				stmt = conn.prepareStatement(sql);
				ResultSet rs = stmt.executeQuery();
				if (rs.next()) {
					suiteId = rs.getInt("suiteId");
				}
				rs.close(); // 如果不关闭这个rs , 就会: java.sql.SQLException: SQL logic
				// error or missing database
				stmt.close();
				System.out.println("suiteId=========" + suiteId);

				// 加 TestDetail 数据
				String sql2 = "insert into PageTestDetail (suiteId,place,roundNo,url,testtime,loadtime) "
						+ "values(?,?,?,?,'testtime',?);";
				stmt = conn.prepareStatement(sql2);

				Iterator<String> keys = detail.keys();
				while (keys.hasNext()) {
					String key = keys.next();
					String value = detail.getString(key);

					stmt.setInt(1, suiteId);
					stmt.setString(2, place);
					stmt.setString(3, roundNo);
					stmt.setString(4, key);
					stmt.setString(5, value);
					stmt.execute();
					// conn.commit();
				}
				try {
					conn.commit();
				} catch (SQLException e) {
					e.printStackTrace();
					conn.rollback(); // 操作不成功，回滚事务
				}
				stmt.close();

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (JSONException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} // 结束数据库的连接
		}
	}

	static private String getDataFromGae(String strUrl) {
		HttpURLConnection jconn = null;
		ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();

		String strRead = "{}";

		try {
			URL url = new URL(strUrl);

			jconn = (HttpURLConnection) url.openConnection();
			jconn.setDoOutput(true);
			jconn.setDoInput(true);
			jconn.connect();

			InputStream in = jconn.getInputStream();
			byte[] buf = new byte[4096];

			int bytesRead;
			while ((bytesRead = in.read(buf)) != -1) {
				byteArrayOutputStream.write(buf, 0, bytesRead);
			}
			strRead = new String(byteArrayOutputStream.toByteArray());

			System.out.println("--------------" + strRead);
		} catch (MalformedURLException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			jconn.disconnect();
			try {
				byteArrayOutputStream.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return strRead;
	}
}
